﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace DataAccess
{
    public class AccessHelper
    {
        private static string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=well_info.accdb;Persist Security Info=False";
        private static OleDbConnection oc;
        private static List<string> wellIDs;
        private static void connToAcc()
        {
            oc = new OleDbConnection(constr);
            oc.Open();

        }
        public static void ConnClose()
        {
            oc.Close();
        }

        public static List<string> getAllWellID(string tabName)
        {
            List<string> wellIDs = new List<string>();
            oc = new OleDbConnection(constr);
            oc.Open();

            string sql = "select distinct well_ID from " + tabName;
            OleDbCommand cmd = new OleDbCommand(sql, oc);
            OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            try
            {
                oda.Fill(ds);
                dt = ds.Tables[0];

                DataRow dr = null;
                foreach (DataRow row in dt.Rows)
                {
                    wellIDs.Add(row[0].ToString());
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "读取文件时发生错误");
                oc.Close();
            }
            finally
            {
                oc.Close();
            }
            return wellIDs;
        }
        /// <summary>
        /// 通用整表数据查询
        /// </summary>
        /// <param name="tabName"></param>
        /// <returns></returns>
        public static DataTable commSearch(string tabName)
        {
            connToAcc();

            string sql = "select * from " + tabName;
            OleDbCommand cmd = new OleDbCommand(sql, oc);
            OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            oda.Fill(ds);
            dt = ds.Tables[0];
            return dt;
        }

        /// <summary>
        /// 通用单行数据插入
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static int commInsert(Object entity, string tableName)
        {
            connToAcc();

            //字符串拼接获取sql语句
            Type t = entity.GetType();
            string[] fields = getFieldNames(t);
            string insertSql = "insert into " + tableName + " (";
            for (int i = 0; i < fields.Length; i++)
            {
                if (i != fields.Length - 1)
                {
                    insertSql += (fields[i] + ",");
                }
                else
                {
                    insertSql += (fields[i] + ") ");
                }
            }
            insertSql += "values (";

            Type[] fieldTypes = getFieldTypes(t);
            for (int i = 0; i < fieldTypes.Length; i++)
            {
                if (i != fieldTypes.Length - 1)
                {
                    if (fieldTypes[i].Name.Equals("String"))
                    {
                        insertSql += "'" + entity.GetType().GetField(fields[i], BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entity) + "',";
                    }
                    else
                    {
                        insertSql += entity.GetType().GetField(fields[i], BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entity) + ",";
                    }
                }
                else
                {
                    if (fieldTypes[i].Name.Equals("String"))
                    {
                        insertSql += "'" + entity.GetType().GetField(fields[i], BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entity) + "')";
                    }
                    else
                    {
                        insertSql += entity.GetType().GetField(fields[i], BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entity) + ")";
                    }
                }
            }

            //执行sql语句
            OleDbCommand insertComm = new OleDbCommand(insertSql, oc);
            int result = insertComm.ExecuteNonQuery();
            oc.Close();
            return result;

        }

        //获取类的字段的类型
        private static Type[] getFieldTypes(Type t)
        {
            ArrayList typeList = new ArrayList();
            foreach (FieldInfo fi in t.GetFields(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic))
            {
                typeList.Add(fi.FieldType);
            }
            Type[] fieldTypes = (Type[])typeList.ToArray(typeof(Type));
            return fieldTypes;
        }
        //获得类的字段的名称
        private static string[] getFieldNames(Type t)
        {
            ArrayList fieldNameList = new ArrayList();
            foreach (FieldInfo fi in t.GetFields(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic))
            {
                fieldNameList.Add(fi.Name);
            }
            string[] fieldNames = (string[])fieldNameList.ToArray(typeof(string));
            return fieldNames;

        }

        /// <summary>
        /// 通用删除
        /// </summary>
        /// <param name="condStr"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static int commDelete(string condStr, string tableName)
        {
            connToAcc();

            string deleStr = "delete * from " + tableName + "where " + condStr;
            OleDbCommand deleteComm = new OleDbCommand(deleStr, oc);
            int result = deleteComm.ExecuteNonQuery();
            oc.Close();
            return result;
        }

        /// <summary>
        /// 数据导入(删除数据并添加多条记录)的事务管理
        /// </summary>
        /// <param name="condStr"></param>
        /// <param name="tableName"></param>
        /// <param name="entities"></param>
        /// <returns></returns>
        public static bool inputTransactionOp(string condStr, string tableName, Object[] entities)
        {
            OleDbCommand cmd = new OleDbCommand();
            OleDbTransaction transaction = null;
            bool result;
            try
            {
                connToAcc();

                //开始事务
                transaction = oc.BeginTransaction();
                cmd.Transaction = transaction;
                cmd.Connection = oc;

                //执行sql语句序列
                //首先删除数据表中的数据
                string deleStr = "delete * from " + tableName + " where " + condStr;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = deleStr;
                if (cmd.ExecuteNonQuery() < 0)
                {
                    throw new Exception();
                }
                //再添加多条数据记录
                foreach (Object entity in entities)
                {
                    //字符串拼接获取sql语句
                    Type t = entity.GetType();
                    string[] fields = getFieldNames(t);
                    string insertSql = "insert into " + tableName + " (";
                    for (int i = 0; i < fields.Length; i++)
                    {
                        if (i != fields.Length - 1)
                        {
                            insertSql += (fields[i] + ",");
                        }
                        else
                        {
                            insertSql += (fields[i] + ") ");
                        }
                    }
                    insertSql += "values (";

                    Type[] fieldTypes = getFieldTypes(t);
                    for (int i = 0; i < fieldTypes.Length; i++)
                    {
                        if (i != fieldTypes.Length - 1)
                        {
                            if (fieldTypes[i].Name.Equals("String"))
                            {
                                insertSql += "'" + entity.GetType().GetField(fields[i], BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entity) + "',";
                            }
                            else
                            {
                                insertSql += entity.GetType().GetField(fields[i], BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entity) + ",";
                            }
                        }
                        else
                        {
                            if (fieldTypes[i].Name.Equals("String"))
                            {
                                insertSql += "'" + entity.GetType().GetField(fields[i], BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entity) + "')";
                            }
                            else
                            {
                                insertSql += entity.GetType().GetField(fields[i], BindingFlags.NonPublic | BindingFlags.Instance).GetValue(entity) + ")";
                            }
                        }
                    }

                    //执行sql语句
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = insertSql;
                    if (cmd.ExecuteNonQuery() < 0)
                    {
                        throw new Exception();
                    }
                }
                //提交事务
                transaction.Commit();
                result = true;
            }
            catch
            {
                //当执行某条语句出现错误时(抛出错误)
                result = false;
                //回滚事务
                transaction.Rollback();
            }
            finally
            {
                //关闭数据库连接并销毁cmd和transaction
                oc.Close();
                cmd.Dispose();
                transaction.Dispose();
            }
            return result;
        }
    }
}
